﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;


namespace NG
{
    class SubDepratmentHandler:ISubDepartmentHandler
    {
        private string tableName = "[dbo].[SubDepartmentType]";

      public  void addSubDepartment(SubDepartment dep)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "INSERT INTO " + tbn + " (DepCode,SubDepCode,SubDepName) VALUES (" + dep.DepCode + "," + dep.SubDepCode + ",'" + dep.SubDepName + "');";

            
            try
            {
                SqlCommand cmd = new SqlCommand(query, con.connection);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public   void deleteSubDepartment(SubDepartment dep)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;

            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "DELETE FROM " + tbn + " WHERE SubDepCode = " + dep.SubDepCode;
            try
            {
                SqlCommand cmd = new SqlCommand(query, con.connection);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }

        }
        public  void updateName(SubDepartment dep, string name)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("UPDATE " + tbn + " SET SubDepName = '{0}' WHERE SubDepCode = {1} ", name, dep.SubDepCode);

            try
            {
                SqlCommand cmd = new SqlCommand(query, con.connection);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }


        public LinkedList<SubDepartment> allSubDeps(string sortedBy, bool desc ,Department dep )
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = "{0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<SubDepartment> sdepsList = new LinkedList<SubDepartment>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn +" WHERE DepCode = " + dep.DepNum+ "ORDER BY " + sorted + extra ;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {

                    SubDepartment sdep = new SubDepartment(int.Parse(rdr["DepCode"].ToString()), int.Parse(rdr["SubDepCode"].ToString()), rdr["SubDepName"].ToString());


                    sdepsList.AddLast(sdep);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return sdepsList;
        }
    }
}
